﻿//2009-12-18    Connection With AP    By Victor
//2010-04-15    新增通訊狀態查詢
//2010-07-01    新增通訊狀態，包含GrpNo欄位
//2011-01-06    QueryCurValue-1新增防呆機制

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

//Using Namespace
using System.Data;
using System.Data.SqlClient;

namespace DataAccess
{
    public class ConnAP
    {
        private string strSCADADSN = System.Configuration.ConfigurationManager.AppSettings["strDSN"];
        string strSQL;

        //功能說明：顯示即時資料    相關Table：『CurValue_cmd』、『CurValue_data』、『HisTagCtrl_data』
        #region public bool QueryCurValue        //下查詢監控設備即時值之指令，回傳成功與否（需等待約1~3秒，才可進行查詢即時資料GetCurValue）
        /// <summary>
        /// 下查詢監控設備即時值之指令，回傳成功與否（需等待約1~3秒)才可進行查詢即時資料
        /// </summary>
        /// <param name="GrpNo">群組編號</param>
        /// <param name="SiteID">案場編號</param>
        /// <returns>回傳查詢成功與否</returns>
        public bool QueryCurValue(string GrpNo, string SiteID)
        {
            try
            {
                //新增例外處理
                strSQL = "Select * From GroupConfig_cfg Where GrpNo = '" + GrpNo + "' and SiteID='" + SiteID + "'";
                if (GrpNo.Trim() == "" || new CommonFun().GenDataTable(strSQL).Rows.Count == 0)
                {
                    return false;
                }

                strSQL = "Select * From CurValue_cmd Where GrpNo = '" + GrpNo + "' and SiteID='" + SiteID + "' and  DATEDIFF(second, CAST(UpdateTime AS dateTime), CAST(GETDATE() AS dateTime)) < 7 ";
                if (new CommonFun().GenDataTable(strSQL).Rows.Count == 0)
                {
                    strSQL = "Insert CurValue_cmd (GrpNo, SiteID) Values ('" + GrpNo + "','" + SiteID + "')";
                    return new CommonFun().ExeSQL(strSQL);
                }
                else
                {
                    strSQL = "Update CurValue_cmd Set UpdateTime = GetDate() Where GrpNo ='" + GrpNo + "' and SiteID ='" + SiteID + "'";
                    return new CommonFun().ExeSQL(strSQL);
                }
                
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg ("QueryCurValue -1", ex.ToString(), strSQL);
                return false;
            }
        }

        /// <summary>
        /// 根據欲查詢監控設備即時值之群組編號及持續查詢時間，回傳成功與否（需等待約1~3秒)才可進行查詢即時資料
        /// </summary>
        /// <param name="GrpNo">群組編號</param>
        /// <param name="SiteID">案場編號</param>
        /// <param name="QueryMins">持續查詢幾分鐘(最高設定為10分鐘）</param>
        /// <returns>回傳查詢成功與否</returns>
        public bool QueryCurValue(string GrpNo, string SiteID, int QueryMins)
        {
            //新增例外處理
            if (QueryMins > 10)
            {
                return false;
            }

            try
            {
                strSQL = "Select * From CurValue_cmd Where GrpNo = '" + GrpNo + "' and SiteID='" + SiteID + "'";
                if (new CommonFun().GenDataTable(strSQL).Rows.Count == 0)
                {
                    //未存在則新增Command
                    strSQL = "Insert CurValue_cmd (GrpNo, SiteID, UpdateTime) Values ('" + GrpNo + "','" + SiteID + "','" + DateTime.Now.AddMinutes(QueryMins).ToString("yyyy/MM/dd HH:mm:ss") + "')";
                    return new CommonFun().ExeSQL(strSQL);
                }
                else
                {
                    //已存在則修改Command UpdateTime
                    strSQL = "Update CurValue_cmd Set UpdateTime = '" + DateTime.Now.AddMinutes(QueryMins).ToString("yyyy/MM/dd HH:mm:ss") + "' Where GrpNo ='" + GrpNo + "' and SiteID='" + SiteID + "'";
                    return new CommonFun().ExeSQL(strSQL);
                }
            }
            catch (Exception ex)
            {
                new CommonFun ().AddErrorMsg("QueryCurValue -2", ex.ToString(), strSQL);
                return false;
            }
        }
        #endregion

        #region public DataTable GetCurValue        //讀取即時值
        /// <summary>
        /// 讀取G即時值
        /// </summary>
        /// <param name="GrpNo">群組編號</param>
        /// <param name="SiteID">案場編號</param>
        /// <returns>回傳該群組下之監控點資料</returns>
        public DataTable GetCurValue(string GrpNo, string SiteID)
        {
            try
            {
                strSQL = "Select CurValue_data.GrpNo, CurValue_data.ObjNo, CurValue_data.SiteID, TagConfig_cfg.bytUse, GrpName, CurValue_data.TagName, sngVal, TagConfig_cfg.strDescription, UpdateTime From CurValue_data Join TagConfig_cfg On CurValue_data.GrpNo = TagConfig_cfg.GrpNo and CurValue_data.ObjNo = TagConfig_cfg.ObjNo and CurValue_data.SiteID = TagConfig_cfg.SiteID";
                strSQL += " Where CurValue_data.GrpNo ='" + GrpNo + "' and CurValue_data.SiteID='" + SiteID + "'";
                return new CommonFun().GenDataTable(strSQL);
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg("GetCurValue", ex.ToString(), strSQL);
                return null;
            }
        }
        #endregion

        #region public DataTable GetAllCurValue        //讀取全部即時值，回傳相關Data Table
        /// <summary>
        /// 讀取全部即時值，回傳相關Data Table
        /// </summary>
        /// <returns>回傳所有目前回傳之即時監控點資料</returns>
        public DataTable GetAllCurValue()
        {
            try
            {
                strSQL = "Select CurValue_data.GrpNo, CurValue_data.ObjNo, CurValue_data.SiteID, TagConfig_cfg.bytUse, GrpName, CurValue_data.TagName, sngVal, TagConfig_cfg.strDescription, UpdateTime From CurValue_data Join TagConfig_cfg On CurValue_data.GrpNo = TagConfig_cfg.GrpNo and CurValue_data.ObjNo = TagConfig_cfg.ObjNo and CurValue_data.SiteID = TagConfig_cfg.SiteID";
                return new CommonFun().GenDataTable(strSQL);
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg("GetAllCurValue", ex.ToString(), strSQL);
                return null;
            }
        }
        #endregion

        #region public DataTable QueryTagCtrl       //查詢監控點起停紀錄，回傳相關Data Table
        /// <summary>
        /// 查詢監控點起停紀錄
        /// </summary>
        /// <returns>回傳相關Data Table</returns>
        public DataTable QueryTagCtrl()
        {
            try
            {
                strSQL = "Select * From HisTagCtrl_data ";
                return new CommonFun().GenDataTable(strSQL);
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg("QueryTagCtrl", ex.ToString(), strSQL);
                return null;
            }
        }
        #endregion



        //功能說明：查詢通訊狀態        相關Tabel：『CurComm_data』、『MPLCConfig_cfg』、『DevConfig_cfg』
        #region public DataTable QueryPLCStatus()          //查詢PLC通訊狀態，回傳相關DataTable        
        /// <summary>
        /// 查詢PLC通訊狀態，回傳相關DataTable
        /// </summary>
        /// <returns>回傳相關資料</returns>
        public DataTable QueryPLCStatus()
        {
            try
            {
                strSQL = "SELECT Web_Site.SiteID, Web_Site.SiteName, CurComm_data.*, MPLCConfig_cfg.MPLCName, MPLCConfig_cfg.strIP, MPLCConfig_cfg.strPort, MPLCConfig_cfg.strPlace, Case CurComm_data.bytAlarm When '1' Then '中斷' When '0' Then '正常' End as Status From CurComm_data ";
                strSQL += " Left Join MPLCConfig_cfg ON CurComm_data.MPLCNo = MPLCConfig_cfg.MPLCNo ";
                strSQL += " Join Web_Site On MPLCConfig_cfg.SiteID = Web_Site.SiteID ";
                strSQL += " Where DevNo = '0' and MPLCConfig_cfg.bytUse ='1'";
                return new CommonFun().GenDataTable(strSQL);
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg("QueryPLCStatus", ex.ToString(), strSQL);
                return null;
            }
        }
        #endregion

        #region public DataTable QueryDevStatus       //查詢PLC底下Dev之通訊狀態，回傳相關DtatTable       
        /// <summary>
        /// 查詢PLC底下Device之通訊狀態，回傳相關DtatTable
        /// </summary>
        /// <param name="MPLCNo">PLC編號</param>
        /// <returns>回傳相關資料</returns>
        public DataTable QueryDevStatus(string MPLCNo)
        {
            try
            {
                strSQL = "SELECT Web_Site.SiteID, Web_Site.SiteName, CurComm_data.*, DevConfig_cfg.DevName, Case CurComm_data.bytAlarm When '1' Then '中斷' When '0' Then '正常' End as Status From CurComm_data ";
                strSQL += " Left Join DevConfig_cfg ON CurComm_data.MPLCNo = DevConfig_cfg.MPLCNo and CurComm_data.DevNo = DevConfig_cfg.DevNo ";
                strSQL += " Join Web_Site On DevConfig_cfg.SiteID = Web_Site.SiteID ";
                strSQL += " Where CurComm_data.DevNo <> '0' and DevConfig_cfg.bytUse ='1' and CurComm_data.MPLCNo ='" + MPLCNo + "'";
                return new CommonFun().GenDataTable(strSQL);
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg("QueryDevStatus", ex.ToString(), strSQL);
                return null;
            }
        }
        #endregion

        #region public DataTable QueryCommData() 通訊狀態並包含GrpNo欄位
        public DataTable QueryCommData()
        {
            strSQL = "Select a.MPLCNo, a.DevNo, a.bytAlarm, a.SiteID, b.GrpNo From CurComm_data a " + 
                     "join TagConfig_cfg b on a.MPLCNo = b.MPLCNo and a.DevNo = b.DevNo " + 
                     "Group By a.MPLCNo, a.DevNo, a.bytAlarm, a.SiteID, b.GrpNo ";
            DataTable dtMapping = new CommonFun().GenDataTable(strSQL);
            return dtMapping;
        }
        #endregion
    }
}
